package lzj.tools;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Record;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil {

  private static Workbook workbook;
  private static JSONArray sheetArray;
  private static HashMap<String, JSONObject> titles;
  private static HashMap<String, List<Record>> sheets;


  public ExcelUtil() {
    titles = new HashMap<>();
    sheets = new HashMap<>();
    sheetArray = new JSONArray();
  }

  public  ExcelUtil fromFile(String path) {
    ExcelUtil result = null;

    if (StrKit.isBlank(path)) {
      return result;
    }

    File file = new File(path);
    if (!file.exists()) {
      return result;
    }

    result = new ExcelUtil();
    result = fromFile(file) ? result : null;

    return result;
  }

  /**
   * 调用这个进行excel文件的解析
   * @param file
   * @return
   */
  private  boolean fromFile(File file) {
    try {
      String name = file.getName().toLowerCase();
      InputStream in = new FileInputStream(file);
      if (name.toLowerCase().endsWith(ext.xls.toString())) {
        workbook = new HSSFWorkbook(in);
      } else if (name.toLowerCase().endsWith(ext.xlsx.toString())) {
        workbook = new XSSFWorkbook(in);
      }
    } catch (Exception e) {
      return false;
    }

    return flush();
  }

  /**
   * 重新解析 Excel 文件
   *
   * @return 是否成功
   */
  public  boolean flush() {
    return flush(-1, null);
  }

  private  boolean flush(int rowOffset, JSONArray cellIndex) {
    boolean result = false;

    if (null == workbook) {
      return result;
    }

    sheetArray.clear();

    int sheetSize = workbook.getNumberOfSheets();
    for (int i = 0; i < sheetSize; ++i) {
      Sheet sheet = workbook.getSheetAt(i);
      String name = sheet.getSheetName();

      sheetArray.add(i, name);

      JSONObject title = new JSONObject();
      titles.put(name, title);

      List<Record> content = new ArrayList<>();
      sheets.put(name, content);

      int rowCount = 0;
      int rowSize = sheet.getLastRowNum();

      // [Neo] j <= rowSize
      for (int j = rowOffset > 0 ? rowOffset : 0; j <= rowSize; ++j) {
        Row row = sheet.getRow(j);

        // [Neo] no-row-data
        if (null == row) {
          continue;
        }

        int cellCount = 0;
        int index = (null != cellIndex) ? cellIndex.size() : -1;

        // [Neo] parse title row
        if (0 == j || rowOffset == j) {
          int count = 0;

          int cellSize = row.getLastCellNum();
          for (int k = 0; k < cellSize; ++k) {

            boolean ignore = false;
            if (index > 0) {
              ignore = true;
              for (int z = 0; z < index; ++z) {
                if (k == cellIndex.getIntValue(z)) {
                  ignore = false;
                  break;
                }
              }
            }

            if (ignore) {
              continue;
            }

            Cell cell = row.getCell(k);

            String value = "-";
            if (null != cell && !StrKit.isBlank(cell.toString())) {
              value = cell.toString();
            }

            title.put("col" + (count++), value);
          }

          continue;
        }

        Record record = new Record();
        content.add(rowCount++, record);

        int lastCellType = -1;
        double lastCellInNumber = 0.f;
        String lastCellInString = "";
        boolean lastCellInBoolean = false;

        int cellSize = row.getLastCellNum();
        for (int k = 0; k < cellSize; ++k) {
          Cell cell = row.getCell(k);

          boolean ignore = false;
          if (index > 0) {
            ignore = true;
            for (int z = 0; z < index; ++z) {
              if (k == cellIndex.getIntValue(z)) {
                ignore = false;
                break;
              }
            }
          }

          if (ignore) {
            continue;
          }

          // [Neo] no-cell-data
          if (null == cell) {

            record.set("col" + (cellCount++), null);
            continue;
          }
          switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
              if (HSSFDateUtil.isCellDateFormatted(cell)) {//如果是日期类型
                Date date = cell.getDateCellValue();
                String Datevalue = DateFormatUtils.format(date, "yyyy-MM-dd HH:mm:ss");
                record.set("col" + cellCount, Datevalue);
              } else {//数值类型
                String val = new DecimalFormat("0.#####").format(cell.getNumericCellValue());
                record.set("col" + cellCount, val);
              }
              break;
            case Cell.CELL_TYPE_FORMULA:
              lastCellInNumber = cell.getNumericCellValue();
              record.set("col" + cellCount, lastCellInNumber);
              break;
            case Cell.CELL_TYPE_STRING:
              lastCellInString = cell.getStringCellValue();
              record.set("col" + cellCount, lastCellInString);
              break;

            case Cell.CELL_TYPE_BOOLEAN:
              lastCellInBoolean = cell.getBooleanCellValue();
              record.set("col" + cellCount, lastCellInBoolean);
              break;

            case Cell.CELL_TYPE_BLANK:
            case Cell.CELL_TYPE_ERROR:
            default:
              record.set("col" + cellCount,null);
              break;
          }

          cellCount++;
          lastCellType = cell.getCellType();
        }

      }
    }

    result = true;
    return result;
  }

  public List<Record> getData(String sheet) {
    return null != this.sheets && this.sheets.containsKey(sheet) ? (List)this.sheets.get(sheet) : null;
  }
  /**
   * 获取工作簿列表
   */
  public JSONArray getSheetArray() {
    return sheetArray;
  }


  private enum ext {
    xls,
    xlsx,
  }



/////////////////////////////////////////////////////////////////////////////////////////////////////////////////

  /**
   * 泛用标准导出Excel（其他的参考此方法进行扩展定制）
   * @param sheetName sheet名称
   * @param title 标题
   * @param values 内容
   * @param wb HSSFWorkbook对象
   * @return
   */
  public static XSSFWorkbook getXSSFWorkbook(String sheetName,String []title,String [][]values, XSSFWorkbook wb){

    // 第一步，创建一个XSSFWorkbook，对应一个Excel文件
    if(wb == null){
      wb = new XSSFWorkbook();
    }
    // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
    XSSFSheet sheet = wb.createSheet(sheetName);
    // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
    XSSFRow row = sheet.createRow(0);
    // 第四步，创建单元格，并设置值表头 设置表头居中
    XSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
    //声明列对象
    XSSFCell cell = null;
    //创建标题
    for(int i=0;i<title.length;i++){
      cell = row.createCell(i);
      cell.setCellValue(title[i]);
      cell.setCellStyle(style);
    }
    //创建内容
    for(int i=0;i<values.length;i++){
      row = sheet.createRow(i + 1);//第一行是标题，所以+1
      for(int j=0;j<values[i].length;j++){
        //将内容按顺序赋给对应的列对象
        row.createCell(j).setCellValue(values[i][j]);
      }
    }
    return wb;
  }
}